package cn.edu.sanxiau.www.dao.impl;

import cn.edu.sanxiau.www.dao.UserDao;
import cn.edu.sanxiau.www.model.User;
import frame.DBUtils.JdbcUtils;


import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public User queryUserByUser(User user) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM user WHERE uaername=? and password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUaername());
            pstmt.setString(2, user.getPassword());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user_db = new User();
                user_db.setUser_id(rs.getInt("user_id"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAddress(rs.getString("address"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUaername(rs.getString("uaername"));
                user_db.setPassword(rs.getString("password"));
                return user_db;

            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addUserByUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO  user (user_id,name,sex,address,tel,uaername,password) VALUES(?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user.getUser_id());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getSex());
            pstmt.setString(4, user.getAddress());
            pstmt.setString(5, user.getTel());
            pstmt.setString(6, user.getUaername());
            pstmt.setString(7, user.getPassword());

            int n = pstmt.executeUpdate();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public List<User> queryAllUser() {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            //对象列表
            List<User> userList_db = new ArrayList<User>();

            while (rs.next()) {
                User user_db = new User();
                user_db.setUser_id(rs.getInt("user_id"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAddress(rs.getString("address"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUaername(rs.getString("uaername"));
                user_db.setPassword(rs.getString("password"));
                //放每个对象
                userList_db.add(user_db);
            }
            return userList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int deleteUserByUser_id(int user_id) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "DELETE FROM user WHERE user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user_id);

            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User queryUserByUser_id(int user_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM user WHERE user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user_db = new User();
                user_db.setUser_id(rs.getInt("user_id"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAddress(rs.getString("address"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUaername(rs.getString("uaername"));
                user_db.setPassword(rs.getString("password"));
                return user_db;

            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateUserByUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE user SET name=?,sex=?,address=?,tel=?,uaername=?,password=? WHERE user_id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getSex());
            pstmt.setString(3, user.getAddress());
            pstmt.setString(4, user.getTel());
            pstmt.setString(5, user.getUaername());
            pstmt.setString(6, user.getPassword());
            pstmt.setInt(7, user.getUser_id());

            int n = pstmt.executeUpdate();
            return n;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}
